const express = require('express');
const router = express.Router();
const db = require('../../../MySql');
const xlsx = require('xlsx');
const multer = require('multer');
const upload = multer() // 内存存储
const { generate19DigitId } = require('../../utils/idUtils')

router.post('/qu/qu/import', upload.single('file'), async (req, res) => {
    try {
        // 获取上传的文件
        const file = req.file

        // 解析 Excel
        const workbook = xlsx.read(file.buffer, { type: 'buffer' })
        const sheetName = workbook.SheetNames[0]
        const sheet = workbook.Sheets[sheetName]
        const headers = [
            '题目序号', '题目类型', '题目内容', '整体解析', '题目图片', '所属题库',
            '是否正确项', '选项内容', '选项解析', '选项图片'
        ]
        const data = xlsx.utils.sheet_to_json(sheet, { header: headers, range: 2 })

        // 记录题目序号和生成的id的映射
        const quIdMap = {}

        for (const row of data) {
            // console.log('题目内容:', row['题目内容'])
            // 只在题目内容不为空时处理题目表和题库表
            if (row['题目内容']) {
                
                // 先查是否已存在该题目（可根据题目内容或其他唯一字段）
                const [existRows] = await db.query(
                    'SELECT id FROM el_qu WHERE content = ? LIMIT 1',
                    [row['题目内容']]
                );
                let qu_id;
                if (existRows.length > 0) {
                    // 已存在，取已有id，执行更新
                    qu_id = existRows[0].id
                    await db.query(
                        `UPDATE el_qu SET qu_type=?, image=?, analysis=? WHERE id=?`,
                        [row['题目类型'], row['题目图片'], row['整体解析'], qu_id]
                    );
                } else {
                    // 不存在，插入新题目
                    qu_id = generate19DigitId()
                    await db.query(
                        `INSERT INTO el_qu (id, qu_type, image, content, analysis) VALUES (?, ?, ?, ?, ?)`,
                        [qu_id, row['题目类型'], row['题目图片'], row['题目内容'], row['整体解析']]
                    );
                }
                quIdMap[row['题目序号']] = qu_id

                // 题库表同理，先查再插或更新
                if (row['所属题库']) {
                    const [repoRows] = await db.query(
                        'SELECT id FROM el_qu_repo WHERE qu_id = ? AND repo_id = ? LIMIT 1',
                        [qu_id, row['所属题库']]
                    )
                    let repoId;
                    if (repoRows.length > 0) {
                        repoId = repoRows[0].id;
                        // 可选：更新sort等字段
                    } else {
                        // 查询当前题库最大sort
                        const [sortRows] = await db.query(
                            'SELECT MAX(sort) AS maxSort FROM el_qu_repo WHERE repo_id = ?',
                            [row['所属题库']]
                        );
                        const maxSort = sortRows[0].maxSort || 0;
                        const sort = maxSort + 1;
                        repoId = generate19DigitId();
                        await db.query(
                            `INSERT INTO el_qu_repo (id, qu_id, qu_type, repo_id, sort) VALUES (?, ?, ?, ?, ?)`,
                            [repoId, qu_id, row['题目类型'], row['所属题库'], sort]
                        );
                    }
                }
            }

            // 插入答案表（每一行都插入，qu_id用映射）
            if (row['选项内容']) {
                const qu_id = quIdMap[row['题目序号']]
                
                // 检查qu_id是否存在，如果不存在则跳过该选项行
                // if (!qu_id) {
                //     console.warn(`跳过选项处理：题目序号 "${row['题目序号']}" 对应的题目不存在`)
                //     continue // 跳过当前选项行
                // }
                
                // console.log('选项内容:', quIdMap[row['题目序号']])
                // console.log('题目序号:', row['题目序号'], 'qu_id:', qu_id)

                // 先查是否有该答案
                const [answerRows] = await db.query(
                    'SELECT id FROM el_qu_answer WHERE qu_id = ? AND content = ? LIMIT 1',
                    [qu_id, row['选项内容']]
                )
                if (answerRows.length > 0) {
                    // 已存在，执行更新
                    await db.query(
                        `UPDATE el_qu_answer SET is_right=?, image=?, analysis=? WHERE id=?`,
                        [row['是否正确项'], row['选项图片'], row['选项解析'], answerRows[0].id]
                    )
                } else {
                    // 不存在，插入
                    //const id = generate19DigitId();
                    await db.query(
                        `INSERT INTO el_qu_answer ( qu_id, is_right, image, content, analysis) VALUES ( ?, ?, ?, ?, ?)`,
                        [
                            qu_id,
                            row['是否正确项'],
                            row['选项图片'],
                            row['选项内容'],
                            row['选项解析']
                        ]
                    );
                }
            }
        }
        return res.json({
            "msg": "请求成功！",
            "code": 0,
            "success": true,
        })
    } catch (error) {
        console.error('导入题库失败:', error)
        return res.status(500).json({ 
            message: '导入题库失败'+error.message
         })
    }
})

module.exports = router